package com.bao.DaoImpl;

import com.bao.Dao.RegionDao;
import com.bao.Emtity.region;
import com.jie.util.BaseDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 省市县
 * Created by TangBaoshan on 2018/8/11.
 */
public class RegionDaoImpl implements RegionDao
{
    @Override
    /*
     * 根据id查询省市县名称
     * */
    public region findregion(int id) {
        region r = new region();
        String sql = "SELECT *  FROM region WHERE areaId =?";
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        conn = BaseDao.getConnection();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1, id);
            rs = stat.executeQuery();
            while (rs.next()) {
                r.setAreaId(rs.getInt("areaId"));
                r.setAreaCode(rs.getString("areaCode"));
                r.setAreaName(rs.getString("areaName"));
                r.setLevel(rs.getInt("level"));
                r.setCityCode(rs.getString("cityCode"));
                r.setCenter(rs.getString("center"));
                r.setParentId(rs.getInt("parentId"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        } finally {
            BaseDao.closeConnection(conn, stat, rs);
        }
        return r;


    }

    @Override
    /*
     * 根据省市县名称查询id
     * */
    public int findregionid(String areaName) {
        String sql = "SELECT areaId FROM region WHERE areaName= ?";
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        int num = 0;
        conn = BaseDao.getConnection();
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, areaName);
            rs = stat.executeQuery();
            while (rs.next()) {
                num = rs.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        } finally {
            BaseDao.closeConnection(conn, stat, rs);
        }
        return num;
    }

    @Override
    public region findparentIdregion(int parentId) {
        region r = new region();
        String sql = "SELECT * FROM region WHERE areaId=(SELECT parentId FROM region WHERE areaId=?)";
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        conn = BaseDao.getConnection();
        try {
            stat = conn.prepareStatement(sql);
            stat.setInt(1, parentId);
            rs = stat.executeQuery();
            while (rs.next()) {
                r.setAreaId(rs.getInt("areaId"));
                r.setAreaCode(rs.getString("areaCode"));
                r.setAreaName(rs.getString("areaName"));
                r.setLevel(rs.getInt("level"));
                r.setCityCode(rs.getString("cityCode"));
                r.setCenter(rs.getString("center"));
                r.setParentId(rs.getInt("parentId"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeConnection(conn, stat, rs);
        }
        return r;
    }


}
